﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccessLibrary.Interface;
using DBAccessLibrary.Model;
using System.Data;
using DataAccess;

namespace DBAccessLibrary.Grammar
{
    class SqlGrammar : DBGrammar, IGrammar, ILocalGrammar
    {
        public SqlGrammar(ConnectionModel connect):base(connect)
        { }

        private string GetTypeFilter(FieldEntity field, string sql)
        {
            string typeStr = field.TypeName;
            int columnLength;
            int.TryParse(field.Length, out columnLength);
            string length = field.Length;
            string TypeName = field.TypeName.ToUpper();

            if (TypeName == "VARCHAR2" || TypeName == "VARCHAR")
            {
                typeStr = "VARCHAR";
                length = "(" + field.Length + ")";
            }
            if ((TypeName == "NUMBER" && columnLength < 9) ||
                TypeName == "INT")
            {
                typeStr = "INT";
                length = "";
            }
            else if (TypeName == "NUMBER" && columnLength >= 9)
            {
                typeStr = "DECIMAL";
                length = "(18,2)";
            }

            if (TypeName == "TIMESTAMP" || TypeName == "DATE")
            {
                length = "";
            }
            if (!field.IsNull)
            {
                sql += " Not Null";
            }
            return string.Format(sql, field.TableName, field.Name, typeStr, length);
        }
        #region IGrammar 成员

        public string AddColumn(FieldEntity field)
        {
            string sql = "ALTER TABLE [{0}] ADD {1} {2}{3}";
           
            if (!string.IsNullOrEmpty(field.DefaultValue))
            {
                sql += " DEFAULT " + field.DefaultValue;
            }
            return GetTypeFilter(field, sql);
        }

        public IEnumerable<string> EditColumn(FieldEntity field)
        {
            List<string> sql = new List<string>();

            if (field.IsChange(o => o.ColumnName))
            {
                //重命名 列
                sql.Add(string.Format("EXEC SP_RENAME '{0}.{1}','{2}','COLUMN'",
                    field.TableName,
                    field.GetNoChangeValue(o=>o.ColumnName),
                    field.ColumnName));
            }
            if (field.IsChange(o => o.DefaultValue))
            {
                //删除约束
                string defStr;
                //if (field.IsForeignKey || field.IsPrimaryKey)
                //{
                defStr = string.Format("select b.name from syscolumns a,sysobjects b where a.id=object_id('{0}') and b.id=a.cdefault and a.name ='{1}' and b.name like 'DF%' ",
                    field.TableName, field.Name);
                DataSet ds = DBOperatorFactory.GetOperator.ExecSqlToDS(defStr, base._connect);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string drop = string.Format("alter table {0} drop constraint {1} ", field.TableName,
                                                row[0]);
                    sql.Add(drop);
                }
                //}
                sql.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
                if (!string.IsNullOrEmpty(field.DefaultValue))
                {
                    defStr = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT DF_{1}_{0} DEFAULT({2}) FOR {1}",
                                           field.TableName, field.Name, field.DefaultValue);
                    sql.Add(defStr);
                }
            }
            else
            {
                sql.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
            }
            return sql;
        }

        public IEnumerable<string> RemoveColumn(FieldEntity field)
        {
            List<string> sql = new List<string>();

            //删除约束
            if (field.IsForeignKey || field.IsPrimaryKey || !string.IsNullOrEmpty(field.DefaultValue))
            {
                string text = string.Format("select b.name from syscolumns a,sysobjects b where a.id=object_id('{0}') and b.id=a.cdefault and a.name ='{1}' and b.name like 'DF%' ",
                    field.TableName, field.Name);
                DataSet ds = DBOperatorFactory.GetOperator.ExecSqlToDS(text, base._connect);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string drop = string.Format("alter table {0} drop constraint {1} ", field.TableName,
                                                row[0]);
                    sql.Add(drop);
                }
            }

            sql.Add(string.Format("ALTER TABLE [{0}] DROP COLUMN {1}", field.TableName, field.TransName));
            return sql;
        }
        public IEnumerable<string> EditPrimary(TableEntity table)
        {
            List<string> sql = new List<string>();
            string pk = table.PrimaryKeyName;
            string[] parimaryKey = table.FileNodes.Where(o => o.IsPrimaryKey).Select(o => o.Name).ToArray();
            if (parimaryKey.Length > 0)
            {
                //删除约束
                if (!string.IsNullOrEmpty(pk))
                {
                    string drop = string.Format("alter table [{0}] drop constraint {1} ", table.Name, pk);
                    sql.Add(drop);
                }
                else
                {
                    pk = "PK" + parimaryKey[0];
                }
                sql.Add(string.Format("ALTER TABLE [{0}] ADD CONSTRAINT {1} PRIMARY KEY({2})",
                    table.Name,
                    pk,
                    string.Join(",", parimaryKey)));
            }
            return sql;
        }

        public string SelectTop(TableEntity table, int count)
        {
            string sql  = "SELECT TOP {1} * FROM {0}";
            return string.Format(sql, table.TransName, count);
        }

        public string GetChTableSql
        {
            get
            {
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {

                    return "SELECT TABLENAME,CHINESETABLENAME as CHINATABLENAME,FIELDNAME as COLUMNNAME,CHINESENAME as CHINACOLUMNNAME " +
                              "FROM T_FIELD_DICTIONARY order by TABLENAME";

                }
                return _xml.ChDescription;
            }
        }

        public string IsExistChTableSql
        {
            get
            {
                string sql = string.Empty;
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {
                    sql = "select name from sysobjects where name='T_FIELD_DICTIONARY'";
                }
                return sql;
            }
        }

        public string AppendTabText(string text)
        {
            return base.TabText(text);
        }

        public string GetErrorInfo(string errorMsg)
        {
            return string.Empty;
        }

        public string GetProcParamInfo(ProcEntity entity)
        {
            if (entity == null)
                return string.Empty;
            string sql;
            if (entity.ChildNodes.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (ProcParamEntity param in entity.ChildNodes)
                {
                    if (param.Status == "IN")
                    {
                        list.Add(string.Format("{0}[{1}({2})]", Environment.NewLine, param.Name, param.TypeName));
                    }
                    else
                    {
                        list.Add(string.Format("{0}[{1}({2})-OUT]", Environment.NewLine, param.Name, param.TypeName));
                    }
                }
                sql = "(" + string.Join(",", list.ToArray()) + ")";
            }
            else
            {
                sql = "()";
            }
            return string.Format("{0} {1}{2}",
                                 "EXEC",
                                 entity.Name,
                                 sql);
        }

      

      
        public string GetPageingSql(string sql, decimal index, decimal count)
        {
            string newSql = "SELECT tab.* FROM (SELECT {0} ROWNUMBER,t.* FROM (" + Environment.NewLine + "{1}" + Environment.NewLine + ") t) tab  WHERE tab.ROWNUMBER >= " + index + " and tab.ROWNUMBER < " + count;

            int form = sql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
            string columns = sql.Substring(0,form);
            if(columns.IndexOf("count(") > 0 || columns.IndexOf("sum(") > 0)
                return sql;
            //sql语句 嵌套中如果存在order by 那么必须 加 TOP关键字，否则报错
            if (sql.IndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase) > 0)
            {
                sql = sql.Trim();
                if (sql.Substring(0, 6).ToLower() == "select")
                {
                    if (sql.Substring(6).Trim().StartsWith("TOP "))
                    {
                        //如果存在TOP 就不需要加 TOP 了
                    }
                    else
                    {
                        sql = sql.Insert(6, " TOP " + count + " ");
                    }
                }
            }
            newSql = string.Format(newSql, "ROW_NUMBER() OVER(ORDER BY GETDATE())", sql);

            return newSql;
        }

        protected override string GetRowNumUpdate(string tableName, string where, string column, string rownumber)
        {

            return string.Format("UPDATE T1 SET {1} FROM ({0}) T1 WHERE T1.ROWNUMBER={2}",
                                string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}", tableName) + " " + where,
                                column,
                                rownumber);

        }
        protected override string GetRowNumDelete(string tableName, string where, string rownumber)
        {
            return string.Format("WITH [T1] AS ({0}) DELETE FROM [T1] WHERE T1.ROWNUMBER={1}",
                                string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}",tableName) + " " + where,
                                rownumber);

        }
        #endregion

        #region ILocalGrammar 成员

        public DataAccess.DatabaseType GetConnectionStr(out string connectStr)
        {
            connectStr = "Database={0};user id={1};password={2};server={3}";


            connectStr = string.Format(connectStr,
                                             _connect.DataBase,
                                             _connect.UserID,
                                             _connect.PassWord,
                                             _connect.Server);


            return DatabaseType.MSSQLServer;

        }

        #endregion

    }
}
